#!/usr/bin/env python
#-*-encoding:utf-8-*-
'''
Created on 2015年3月29日

@author: chenyongbing
'''
import sys,os,re,commands,datetime
reload(sys)  
sys.setdefaultencoding('utf8')  
#current_dir = os.path.dirname(__file__)
current_dir = os.path.split(os.path.realpath(__file__))[0]
import logging
logging.basicConfig(level = logging.DEBUG)
sys.path.append(os.path.join(current_dir,'../../'))
from base.localConfig import baseLocalConfig
zhimind_host = baseLocalConfig.mysqlConfig['zhimind']['host']

zhimind_user = baseLocalConfig.mysqlConfig['zhimind']['user']
zhimind_passwd = baseLocalConfig.mysqlConfig['zhimind']['passwd']
zhimind_db = baseLocalConfig.mysqlConfig['zhimind']['db']
class ExportMacs(object):
    '''
        该工具提供了mac地址导出
    '''


    def __init__(self,mysql_host='localhost',mysql_user='root',mysql_passwd='',mysql_db='zhimind',testing = False):
        '''
        Constructor
        '''
        self.mysql_host = mysql_host
        self.mysql_user = mysql_user
        self.mysql_passwd = mysql_passwd
        self.mysql_db = mysql_db
        self.testing = testing
    
    def conv_tuple_to_str(self,alist=[]):
        if len(alist)==0:
            logging.error('List Is Null.')
            return ''
        
        if len(alist)==1:
            return '"%s"'%alist[0]
        blist = []
        for a in alist:
            blist.append(str(a))
        return '"'+'","'.join(blist)+'"'
    
    
    def get_group_field(self,group_type=4):
        if group_type == 0:
            return  'comp_group'
        elif group_type == 1:
            return 'prov_group'
        elif group_type == 2:
            return 'city_group'
        else:
            return 'dgroup'
    
    
    def get_employee_macs_sql(self,groups=[],startTime=None,endTime=None,rtype='employee'):
        u'''rtype:employee 员工mac地址
                employee_sys 系统计算员工
                employee_real 实际商户手动输入员工
        '''
        query = "select distinct mac into outfile '%s' from mac_employee"
    
    
    def get_all_macs_sql(self,groups=[],startTime=None,endTime=None,group_type=4,if_sha1=False,if_serial_number=False):
#         query_customer = 'select  mac from mac_customer_stats'
#         query_walkby = 'select  mac from mac_walkby_stats'
#         if len(groups)!=0:
#             groups_str = self.conv_tuple_to_str(groups)
#             groupField = self.get_group_field(group_type)
#             query_customer = query_customer + ' where %s in (%s)'%(groupField,groups_str)
#             query_walkby = query_walkby + ' where %s in (%s)'%(groupField,groups_str)
#         if startTime!=None and endTime!=None:
#             if len(groups)==0:
#                 query_customer = query_customer + ' where '
#                 query_walkby = query_walkby + ' where '
#             else:
#                 query_customer = query_customer + ' and '
#                 query_walkby = query_walkby + ' and '
#             query_customer = query_customer + " day between '%s' and '%s'"%(startTime,endTime)
#             query_walkby = query_walkby + " day between '%s' and '%s'"%(startTime,endTime)

        query_customer = self.get_customer_macs_sql(groups=groups, startTime=startTime, endTime=endTime, rtype='customer', group_type=group_type)
        query_walkby = self.get_customer_macs_sql(groups=groups, startTime=startTime, endTime=endTime, rtype='walkby', group_type=group_type)
        
        query = "select distinct "
        if if_sha1:
            query+= 'SHA1(upper(mac))'
        else:
            query += ' mac'
            
        if if_serial_number:
            query += ',@rownum:=@rownum+1 AS rownum from  (SELECT @rownum:=0) r, (%s union all %s) as tab'%(query_customer,query_walkby)
        else:
            query +="  from (%s union all %s) as tab"%(query_customer,query_walkby)
        
        #query = "select distinct mac  from (%s union all %s) as tab"%(query_customer,query_walkby)
        return query
    
    def get_customer_macs_sql(self,groups=[],startTime=None,endTime=None,rtype='customer',group_type=4,avg_dur_range=[None,None],freq_range=[None,None],if_sha1=False,if_serial_number=False):
        if re.search('customer',rtype):
            tab = 'mac_customer_stats'
        else:
            tab = 'mac_walkby_stats'
        
        query = "select distinct "
        if if_sha1:
            query+= 'SHA1(upper(mac))'
        else:
            query += ' mac'
            
        if if_serial_number:
            query += ', @rownum:=@rownum+1 AS rownum from  (SELECT @rownum:=0) r,'
        else:
            query +="  from "
        
        filters = []
        if len(groups)!=0:
            groups_str = self.conv_tuple_to_str(groups)
            #query = query + ' where dgroup in (%s)'%groups_str
            groupField = self.get_group_field(group_type)
            filters.append(' %s in (%s)'%(groupField,groups_str))
            
        if startTime!=None and endTime!=None:
            if startTime != endTime:
                filters.append(" day between '%s' and '%s'"%(startTime,endTime))
            else:
                filters.append(" day ='%s'"%(startTime))
        elif startTime!=None:
            filters.append(" day >= '%s'"%(startTime))
        elif endTime !=None:
            filters.append(" day <= '%s'"%(endTime))
        if rtype == 'new_customer' and startTime != None:
            filters.append(' first_day >="%s"'%(startTime))
        if rtype == 'old_customer' and startTime !=None:
            filters.append(' first_day <"%s"'%(startTime))
            
        if re.search('customer',rtype):
            having_list = []
            min_freq , max_freq = freq_range
            min_avg_dur , max_avg_dur = avg_dur_range
            if min_freq!=None and max_freq!=None and min_freq==max_freq:
                having_list.append(' visit_freq="%s"'%(min_freq))
            elif min_freq!=None and max_freq!=None:
                having_list.append(' visit_freq between "%s" and "%s"'%(min_freq,max_freq))
            elif min_freq!=None:
                having_list.append(' visit_freq>="%s"'%(min_freq))
            elif max_freq!=None:
                having_list.append(' visit_freq<= "%s"'%(max_freq))
                
            
            if min_avg_dur!=None and max_avg_dur!=None and min_avg_dur==max_avg_dur:
                having_list.append(' avg_dur="%s"'%(min_avg_dur))
            elif min_avg_dur!=None and max_avg_dur!=None:
                having_list.append(' avg_dur between "%s" and "%s"'%(min_avg_dur,max_avg_dur))
            elif min_avg_dur!=None:
                having_list.append(' avg_dur>="%s"'%(min_avg_dur))
            elif max_avg_dur!=None:
                having_list.append(' avg_dur<= "%s"'%(max_avg_dur))
            
            if len(having_list)!=0:
                    
                return query + '(select mac ,sum(visit_freq) as visit_freq , sum(sum_dur)/1000/sum(visit_freq) as avg_dur from %s where %s group by mac having %s) as tab'%(tab,' and '.join(filters),' and '.join(having_list))
            else:
                #return query + '(select mac ,sum(visit_freq) as visit_freq , sum(sum_dur)/1000/sum(visit_freq) as avg_dur from %s where %s group by mac) as tab'%(tab,' and '.join(filters))
                return query+'%s where '%tab+' and '.join(filters)
            
        else:     
            return query+'%s where '%tab+' and '.join(filters)
    def export_macs_by_role(self,groups=[],group_type=4,startTime=None,endTime=None,rtype='all',mtype='all',export_file='/tmp/macs.txt',freq_range=[None,None],avg_dur_range=[None,None],if_sha1=False,if_serial_number=False):
        u'''rtype:all 所有mac地址 不包含员工
                customer 顾客mac地址
                walkby 路人mac地址
                employee 员工mac地址
                employee_sys 系统计算员工
                employee_real 实际商户手动输入员工
        '''
        if re.search('customer',rtype):
            query = self.get_customer_macs_sql(groups=groups,group_type=group_type, startTime=startTime, endTime=endTime, rtype=rtype,freq_range=freq_range,avg_dur_range=avg_dur_range,if_sha1=if_sha1,if_serial_number=if_serial_number)
        elif rtype == 'walkby':
            query = self.get_customer_macs_sql(groups=groups,group_type=group_type, startTime=startTime, endTime=endTime, rtype='walkby',if_sha1=if_sha1,if_serial_number=if_serial_number)
        elif re.search('employee',rtype):
            pass
            #query = self.get_employee_macs_sql(groups=groups, startTime=startTime, endTime=endTime, rtype='employee')
        else:
            query = self.get_all_macs_sql(groups=groups,group_type=group_type, startTime=startTime, endTime=endTime,if_sha1=if_sha1,if_serial_number=if_serial_number)
        if rtype in ['customer','walkby','employee']:
            if mtype == 'local':
                query += ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=1'
            elif mtype == 'global':
                query+= ' and substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=0'
        else:
            if mtype == 'local':
                query += ' where substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=1'
            elif mtype == 'global':
                query+= ' where substring(LPAD(conv(left(mac,2),16,2),8,0),7,1)=0'      
        
        logging.debug(query)
        
        cmd = '/data/mysql/bin/mysql -h%s -u%s -p%s %s -e "%s"  > "%s"'%(self.mysql_host,self.mysql_user,self.mysql_passwd,self.mysql_db,query,export_file)
        logging.debug(cmd)
        if self.testing == False:
            while 1:
                out = commands.getstatusoutput(cmd)
                if out[0]==0:
                    logging.debug('sed -i "1d" %s'%export_file)
                    #status = commands.getstatus('sed -i "1d" %s'%export_file)
                    commands.getoutput('sed -i "1d" %s'%export_file)
                logging.error(out[1])
                if re.search('No such file or directory',out[1]):break
                break
            
        else:
            return query
    
    def get_macs_count(self,mfile):
        count = commands.getoutput('cat %s| wc -l'%mfile)
        return int(count)
    
    
    def create_macs_file(self,mfile='',min_line_cnt=0,max_line_cnt=10000,export_file=''):
        fr = open(export_file,'w')
        for line in open(mfile).readlines():
            for mac , count in re.findall('^([^\s]+)\s*([\d]+)$',line):
                #mac,count  = line.split('        ')
                if int(count) > max_line_cnt:break
                fr.write('%s,%s\n'%(mac,count))
            
        fr.close()
    
    def analyze_args(self,args):
        groups = args.groups
        if groups!=None:
            groups = groups.split(',')
        group_type = args.group_type
        startTime = args.startTime
        endTime  = args.endTime
        role = args.role
        mtype = args.mtype
        max_freq = args.max_freq
        min_freq = args.min_freq
        max_dur = args.max_dur
        min_dur = args.min_dur
        export_path = args.export_path
        
        demand_id = args.demand_id
        comp_id = args.comp_id
        today = datetime.datetime.today().strftime('%Y%m%d%H%M')
        export_file = export_path+'macs.%s.%s.%s.txt'%(str(comp_id),str(demand_id),str(today))
        
        if_sha1 = args.if_sha1
        if_serial_number=args.if_serial_number
        
        macs_range = args.macs_range
        
#         
#         groups = [32015520]
#         role = 'new_customer'
#         startTime = '2015-07-20'
#         endTime = '2015-07-20'
#         min_freq = 12
#         max_freq = 13
#         min_dur = None
#         max_dur = 110
#         if_sha1 = True
#         if_serial_number=True
        if groups == None:
            logging.error('You Must Enter A Group.')
            sys.exit(1)
        
        if min_freq != None and max_freq!=None and min_freq> max_freq:
            logging.error('Min Freq %s Must Be Litter Than Mac Freq %s.'%(min_freq,max_freq))
            sys.exit(1)
        
        if min_dur != None and max_dur!=None and min_dur> max_dur:
            logging.error('Min Dur %s Must Be Litter Than Mac Dur %s.'%(min_dur,max_dur))
            sys.exit(1)
        
        groupField  = self.get_group_field(group_type)
        if re.search( 'old',role) and startTime ==None:
            logging.error('While startTime == None ; Old Customer Not Find.')
            sys.exit(1)
        
        if not re.search('^[\d]*:[\d]*$',macs_range):
            logging.error('请输入 分析报告规模(客群数量)')
            sys.exit(1)
        min_line_cnt , max_line_cnt = macs_range.split(':')
        if min_line_cnt.strip() == '':
            min_line_cnt = None
        else:
            min_line_cnt = int(min_line_cnt)
        if max_line_cnt.strip() == '':
            max_line_cnt = None
        else:
            max_line_cnt = int(max_line_cnt)
        export_tmp_file = export_file+'.tmp'
        self.export_macs_by_role(groups=groups, group_type=group_type, startTime=startTime, endTime=endTime, rtype=role, mtype=mtype, export_file=export_tmp_file,freq_range=[min_freq,max_freq],avg_dur_range=[min_dur,max_dur],if_sha1=if_sha1,if_serial_number=if_serial_number)
        
        if os.path.exists(export_tmp_file):
            total_line_cnt = self.get_macs_count(export_tmp_file)
            if max_line_cnt!=None and total_line_cnt > max_line_cnt:
                logging.info('导出的mac地址数量(%s)大于最大值(%s),你需要截取'%(total_line_cnt,max_line_cnt))
#                 sedCmd = 'sed -n "0,%sp" %s > %s'%(max_line_cnt,export_file,export_file)
#                 logging.debug(sedCmd)
#                 commands.getoutput(sedCmd)
                self.create_macs_file(mfile=export_tmp_file, min_line_cnt=min_line_cnt, max_line_cnt=max_line_cnt, export_file=export_file)
                line_cnt = max_line_cnt
            elif min_line_cnt !=None and total_line_cnt < min_line_cnt:
                logging.error('导出的mac地址数量(%s)小于最低值(%s)'%(total_line_cnt,min_line_cnt))
                sys.exit(1)
            else:
                self.create_macs_file(mfile=export_tmp_file, min_line_cnt=min_line_cnt, max_line_cnt=max_line_cnt, export_file=export_file)
                line_cnt = total_line_cnt
            if os.path.exists(export_file):
                data_size = os.path.getsize(export_file)
                logging.debug('scp %s zm12:%s'%(export_file,export_file))
                commands.getoutput('scp %s zm12:%s'%(export_file,export_file))
                
                query = "update analysis set data_size='%s',line_cnt='%s',file_path='%s',status=1,encrypt=2 where data_name='%s'"%(data_size,line_cnt,export_file,demand_id)
            

            else:
                logging.error('create export_file error.')
                query = "update analysis set status=2 where data_name='%s'"%(demand_id)
        else:
            logging.error('create export_file_tmp error.')
            query = "update analysis set status=2 where data_name='%s'"%(demand_id) 
        
        
        logging.error( query)
        cmd = '/data/mysql/bin/mysql -h%s -u%s -p%s %s -e "%s"'%(self.mysql_host,self.mysql_user,self.mysql_passwd,self.mysql_db,query)
        logging.debug(cmd)
        if self.testing==False:
            out = commands.getstatusoutput(cmd)
            logging.debug(out)
        
        '''
        0 ：初始化，及还未调用获取OSS权限之前； 
  -- 1: 数据准备完毕； 2：数据准备失败
        '''
       
myExportMacs = ExportMacs(mysql_host=zhimind_host,mysql_user=zhimind_user,mysql_passwd=zhimind_passwd,mysql_db=zhimind_db)        

if __name__ == '__main__':
    import argparse
    parser = argparse.ArgumentParser(description='args')
    parser.add_argument('--groups',metavar="groups",default=None)
    parser.add_argument('--group_type',metavar="group type:4 门店;2 城市; 1 省份;0 公司",default=4,type=int)
    parser.add_argument('--comp_id',metavar="公司id",default='0')
    parser.add_argument('--startTime',metavar="start time",default=None)
    parser.add_argument('--endTime',metavar="end time",default=None)
    parser.add_argument('--role',metavar="角色:all,walkby,customer,new_customer,old_customer",default='customer')
    parser.add_argument('--max_freq',metavar="最大来访次数",default=None)
    parser.add_argument('--min_freq',metavar="最小来访次数",default=None)
    parser.add_argument('--max_dur',metavar="最大平均停留时间",default=None)
    parser.add_argument('--min_dur',metavar="最小平均停留时间",default=None)
    parser.add_argument('--export_path',metavar="导出路径",default='/data/tmp/')
    parser.add_argument('--demand_id',metavar="需求ID",default='demand_id')
    parser.add_argument('--mtype',metavar="mac地址类别:all,local(随机),global(真实)",default='all')
    parser.add_argument('--macs_range',metavar="mac地址条数区间；闭区间",default='0:10000')
    
    parser.add_argument('--if_sha1',action='store_true',help='是否对mac加密')
    parser.add_argument('--if_serial_number',action='store_true',help='是否对mac添加序号')
    
    parser.add_argument('--testing',action='store_true',help='testing')
    
    
  
    args = parser.parse_args()
    testing = args.testing
   
    myExportMacs.analyze_args(args)
#     
#     groups = []
#     startTime = '2015-07-01'
#     endTime = '2015-07-20'
#     rtype = 'new_customer'
#     export_file = '/tmp/macs.txt'
#     print myExportMacs.export_macs_by_role(groups= groups, startTime = startTime, endTime = endTime,rtype =  rtype, export_file=export_file)